﻿using Entidades;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AccesoDatos
{
    public class ADGuia
    {
        Database oDatabase = DatabaseFactory.CreateDatabase(ConfigurationManager.AppSettings["conexionBD"]);

        public List<ENGuiaSearch> BuscarPeru(ENGuia objBE)
        {
            try
            {
                DbCommand odbcommand = oDatabase.GetStoredProcCommand("usp_Guia_gl_BusquedaPeru");

                if (objBE.ID == 0)                
                    oDatabase.AddInParameter(odbcommand, "@pisID", DbType.String, DBNull.Value);                
                else
                    oDatabase.AddInParameter(odbcommand, "@pisID", DbType.String, objBE.ID.ToString());
                
                if (objBE.Filtro.FechaGuiaIni.HasValue)                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaGuiaIni", DbType.DateTime, objBE.Filtro.FechaGuiaIni.Value);
                else                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaGuiaIni", DbType.DateTime, DBNull.Value);
                
                if (objBE.Filtro.FechaGuiaFin.HasValue)                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaGuiaFin", DbType.DateTime, objBE.Filtro.FechaGuiaFin.Value);                
                else                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaGuiaFin", DbType.DateTime, DBNull.Value);
                
                if (objBE.Filtro.FechaSalidaIni.HasValue)                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaSalidaIni", DbType.DateTime, objBE.Filtro.FechaSalidaIni.Value);                
                else                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaSalidaIni", DbType.DateTime, DBNull.Value);
                
                if (objBE.Filtro.FechaSalidaFin.HasValue)                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaSalidaFin", DbType.DateTime, objBE.Filtro.FechaSalidaFin.Value);                
                else                
                    oDatabase.AddInParameter(odbcommand, "@pidtFechaSalidaFin", DbType.DateTime, DBNull.Value);
                
                if (objBE.Numero > 0)
                    oDatabase.AddInParameter(odbcommand, "@pisNumero", DbType.String, objBE.Numero.ToString());
                else                
                    oDatabase.AddInParameter(odbcommand, "@pisNumero", DbType.String, DBNull.Value);
                
                if (string.IsNullOrEmpty(objBE.Empio.Replace("'", string.Empty)))
                    oDatabase.AddInParameter(odbcommand, "@pisEmpio", DbType.String, DBNull.Value);                
                else                
                    oDatabase.AddInParameter(odbcommand, "@pisEmpio", DbType.String, objBE.Empio.Replace("'", string.Empty));
                
                if (string.IsNullOrEmpty(objBE.Estado.Replace("'", string.Empty)))                
                    oDatabase.AddInParameter(odbcommand, "@pisEstado", DbType.String, DBNull.Value);
                else
                    oDatabase.AddInParameter(odbcommand, "@pisEstado", DbType.String, objBE.Estado.Replace("'", string.Empty));
                
                if (string.IsNullOrEmpty(objBE.Filtro.PeruNombres.Replace("'", string.Empty)))
                    oDatabase.AddInParameter(odbcommand, "@pisPeruNombre", DbType.String, DBNull.Value);
                else
                    oDatabase.AddInParameter(odbcommand, "@pisPeruNombre", DbType.String, objBE.Filtro.PeruNombres.Replace("'", string.Empty));
                
                if (string.IsNullOrEmpty(objBE.Filtro.PeruPaterno.Replace("'", string.Empty)))
                    oDatabase.AddInParameter(odbcommand, "@pisPeruPaterno", DbType.String, DBNull.Value);
                else
                    oDatabase.AddInParameter(odbcommand, "@pisPeruPaterno", DbType.String, objBE.Filtro.PeruPaterno.Replace("'", string.Empty));
                
                if (string.IsNullOrEmpty(objBE.Filtro.PeruTelefono.Replace("'", string.Empty)))
                    oDatabase.AddInParameter(odbcommand, "@pisPeruTelefono", DbType.String, DBNull.Value);
                else
                    oDatabase.AddInParameter(odbcommand, "@pisPeruTelefono", DbType.String, objBE.Filtro.PeruTelefono.Replace("'", string.Empty));
                
                if (string.IsNullOrEmpty(objBE.Filtro.JaponNombres.Replace("'", string.Empty)))
                    oDatabase.AddInParameter(odbcommand, "@pisJaponNombre", DbType.String, DBNull.Value);
                else
                    oDatabase.AddInParameter(odbcommand, "@pisJaponNombre", DbType.String, objBE.Filtro.JaponNombres.Replace("'", string.Empty));
                
                if (string.IsNullOrEmpty(objBE.Filtro.JaponPaterno.Replace("'", string.Empty)))
                    oDatabase.AddInParameter(odbcommand, "@pisJaponPaterno", DbType.String, DBNull.Value);
                else
                    oDatabase.AddInParameter(odbcommand, "@pisJaponPaterno", DbType.String, objBE.Filtro.JaponPaterno.Replace("'", string.Empty));
                
                if (string.IsNullOrEmpty(objBE.Filtro.JaponTelefono.Replace("'", string.Empty)))
                    oDatabase.AddInParameter(odbcommand, "@pisJaponTelefono", DbType.String, DBNull.Value);
                else
                    oDatabase.AddInParameter(odbcommand, "@pisJaponTelefono", DbType.String, objBE.Filtro.JaponTelefono.Replace("'", string.Empty));

                List<ENGuiaSearch> lista = new List<ENGuiaSearch>();
                using (IDataReader reader = oDatabase.ExecuteReader(odbcommand))
                {
                    while (reader.Read())
                        lista.Add(new ENGuiaSearch(reader));
                }
                return lista;


                //string st = ReadToJson(oDatabase.ExecuteReader(odbcommand));
                //st = st.Replace("'", "\"");
                //return st;
                //DataSet sd = oDatabase.ExecuteReader(odbcommand);
                //return sd.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
        }

        public List<ENGuiaFiles> Files(int intID)
        {
            try
            {
                DbCommand odbcommand = oDatabase.GetStoredProcCommand("usp_GuiaFiles_g_Listar");

                oDatabase.AddInParameter(odbcommand, "@guia", DbType.Int32, intID);

                List<ENGuiaFiles> lista = new List<ENGuiaFiles>();
                using (IDataReader reader = oDatabase.ExecuteReader(odbcommand))
                {
                    while (reader.Read())
                        lista.Add(new ENGuiaFiles(reader));
                }
                return lista;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public ENGuiaData Guia(int ID)
        {
            try
            {
                DbCommand odbcommand = oDatabase.GetStoredProcCommand("usp_Guia_g_Data");
                oDatabase.AddInParameter(odbcommand, "@piiID", DbType.Int32, ID);
                
                using (IDataReader reader = oDatabase.ExecuteReader(odbcommand))
                {
                    while (reader.Read())
                        return new ENGuiaData(reader);
                }

                return new ENGuiaData();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public bool DeleteFile(int intID)
        {
            DbCommand oDbCommand = oDatabase.GetStoredProcCommand("usp_GuiaFiles_s_Delete");
            oDatabase.AddInParameter(oDbCommand, "@id", DbType.Int32, intID);
            if (oDatabase.ExecuteNonQuery(oDbCommand) > 0)
                return true;
            return false;
        }

        public bool CreateFile(int intID, string strFile)
        {
            DbCommand oDbCommand = oDatabase.GetStoredProcCommand("usp_GuiaFiles_s_Create");
            oDatabase.AddInParameter(oDbCommand, "@guia", DbType.Int32, intID);
            oDatabase.AddInParameter(oDbCommand, "@nombre", DbType.String, strFile);
            if (oDatabase.ExecuteNonQuery(oDbCommand) > 0)
                return true;
            return false;
        }

        public string ReadToJson(IDataReader reader)
        {
            List<string> cols = new List<string>(10);
            int ncols = reader.FieldCount;
            for (int i = 0; i < ncols; ++i)
            {
                cols.Add(reader.GetName(i));
            }
            StringBuilder sbJson = new StringBuilder("[");
            //process each row
            while (reader.Read())
            {
                sbJson.Append("{");
                foreach (string col in cols)
                {
                    sbJson.AppendFormat("'{0}':{1},", col, reader[col]);
                }
                sbJson.Replace(", ", "},", sbJson.Length - 2, 2);
            }
            sbJson.Replace("},", "}]", sbJson.Length - 2, 2);

            return sbJson.ToString();
        }

    }
}
